package com.exam.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import com.exam.model.PaperModel;
import com.exam.util.Tools;

public class PaperDao extends BaseDao{
	
	/**
	 * 删除试卷
	 * @param id
	 * @return
	 */
	public boolean deletePaper(String id){
		//删除试卷关联的成绩
		String sql = "delete from ex_grade where pid="+id;
		adu(sql);
		//删除试卷
		String sql2 = "delete from ex_paper where id="+id;
		return adu(sql2);
	}
	
	/**
	 * 检测是否有发布试卷
	 * @return
	 * @throws SQLException 
	 */
	public boolean hasTestPaper() throws SQLException{
		boolean b = false;
		String sql = "select id from  ex_paper  where status=1";
		res = query(sql);
		if(res.next()){
			b = true;
		}
		closeDB();
		return b;
	}
	
	/**
	 * 创建试卷
	 * @param paper
	 * @return
	 */
	public boolean createPaper(PaperModel paper){
		String sql = "insert into ex_paper ( " +
				"paper_name," +
				"test_time," +
				"dbid," +
				"questionNum," +
				"summary," +
				"type," +
				"total_score," +
				"ctime," +
				"admin ) values( " +
				"'"+paper.getPaperName()+"'," +
				""+paper.getTestTime()+"," +
				""+paper.getDbid()+"," +
				""+paper.getQuestionNum()+"," +
				"'"+paper.getSummary()+"'," +
				""+paper.getType()+"," +
				""+paper.getTotalScore()+", " +
				"NOW(),'"+getAdmin()+"' )";
		return adu(sql);
	}
	
	
	/**
	 * 获取正在考试的试卷信息
	 * @return
	 * @throws SQLException
	 */
	public PaperModel getTestPaper() throws SQLException{
		PaperModel paper = null;
		String sql = "select  " +
				"paper_name," +
				"test_time," +
				"dbid," +
				"questionNum," +
				"status," +
				"total_score," +
				"ctime," +
				"id," +
				"type " +
				" from ex_paper where status=1 limit 0,1" ;
		res  = query(sql);
		if(res.next()){
			paper = new PaperModel();
			paper.setPaperName(res.getString(1));
			paper.setTestTime(res.getInt(2));
			paper.setDbid(res.getInt(3));
			paper.setQuestionNum(res.getInt(4));
			paper.setStatus(res.getInt(5));
			paper.setTotalScore(res.getInt(6));
			paper.setCtime(res.getString(7));
			paper.setId(res.getInt(8));
			paper.setType(res.getInt(9));
		}
		closeDB();
		return paper;
	}
	
	
	/**
	 * 获取试卷列表
	 * @return ArrayList
	 * @throws SQLException
	 */
	public ArrayList<PaperModel> getPaperList(String key , int status ,String limit) throws SQLException{
		ArrayList<PaperModel> list = new ArrayList<PaperModel>();
		String condition = "";
		if(!key.equals("")){
			condition += " and paper_name like '%"+key+"%'";
		}
		if(status==0 || status==1){
			condition += " and status="+status;
		}
		
		String sql =  "select  " +
				"id," +
				"paper_name," +
				"summary," +
				"test_time," +
				"dbid," +
				"questionNum," +
				"status," +
				"type,"+
				"total_score," +
				"ctime" +
				" from ex_paper where admin='"+getAdmin()+"' "+condition+" order by id desc "+limit ;
		res = query(sql);
		
		while(res.next()){
			PaperModel paper = new PaperModel();
			paper.setId(res.getInt(1));
			paper.setPaperName(res.getString(2));
			paper.setSummary(res.getString(3));
			paper.setTestTime(res.getInt(4));
			paper.setDbid(res.getInt(5));
			paper.setQuestionNum(res.getInt(6));
			paper.setStatus(res.getInt(7));
			paper.setType(res.getInt(8));
			paper.setTotalScore(res.getInt(9));
			paper.setCtime(Tools.formatDate(res.getString(10)));
			list.add(paper);
			
		}
		closeDB();
		return list;
	}
	
	public int getCount(String key , int status){
		String condition = "";
		if(!key.equals("")){
			condition += " and paper_name like '%"+key+"%'";
		}
		if(status==0 || status==1){
			condition += " and status="+status;
		}
		String sql =  "select  count(*) from ex_paper where admin='"+getAdmin()+"' "+condition;
		int m = 0;
		res = query(sql);
		try {
			if(res.next()){
				m = res.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		closeDB();
		return m;
	}
	
	/**
	 * 所有试卷
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<PaperModel> getAllPaper() throws SQLException{
		ArrayList<PaperModel> list = new ArrayList<PaperModel>();
		
		
		String sql =  "select  " +
				"id," +
				"paper_name" +
				" from ex_paper where admin='"+getAdmin()+"' order by id desc " ;
		res = query(sql);
		
		while(res.next()){
			PaperModel paper = new PaperModel();
			paper.setId(res.getInt(1));
			paper.setPaperName(res.getString(2));
			list.add(paper);
		}
		closeDB();
		return list;
	}
	
	/**
	 * 结束考试
	 * @param id
	 * @return
	 */
	public boolean testFinished(String id){
		String sql = "update ex_paper set status=0 where id="+id;
		return adu(sql);
	}
	
	
}
